/**
 * 
 */
package com.hezhi.trafficcopsys.persistence.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.hezhi.framework.dao.BaseDaoImpl;
import com.hezhi.framework.dao.DAOUtil;
import com.hezhi.framework.dao.common.Extractor;
import com.hezhi.framework.exception.DaoException;
import com.hezhi.trafficcopsys.domain.SpecialCar;
import com.hezhi.trafficcopsys.persistence.dao.SpecialCarDao;

/**
 * @author tony
 * 
 */
public class SpecialCarDaoImpl  extends BaseDaoImpl implements SpecialCarDao{
	
	   /**
	 * 
	 */
	private static final long serialVersionUID = -8013560469607424201L;
	
	/**
     * Logging output for this SpecialCar database instance.
     */
    private Log log = LogFactory.getLog(this.getClass());
    
    
    public SpecialCarDaoImpl() {
    	
    	Connection conn = null;
		try {
			conn = DAOUtil.getSQLiteDBConnection();
			
	        String createTblSql = "create table IF NOT EXISTS specialCar ( carNo string,carOwner string,contact string,comments string);";
	        this.createTable(conn, createTblSql);
	        
		} catch (DaoException e) {
			log.error(e.getMessage());
		} finally {
			DAOUtil.closeJDBCConnection(conn);
		}
    }
	
    /**
     * <p>Create and return a new {@link SpecialCar} defined in this SpecialCar database.
     * </p>
     *
     * @param SpecialCarname SpecialCarname of the new SpecialCar
     *
     * @exception IllegalArgumentExceptionif the specified SpecialCarname
     *  is not unique
     */
	@Override
    public int addSpecialCar(SpecialCar SpecialCar) {
		Connection conn = null;
		int intAddNums = 0;
		try {
			conn = DAOUtil.getSQLiteDBConnection();
			
	        String sql = "insert into specialCar (carNo,carOwner,contact,comments) values(?,?,?,?)";
	        intAddNums = this.execute(conn, sql, new Object[]{SpecialCar.getCarNo(),SpecialCar.getCarOwner(),SpecialCar.getContact(),SpecialCar.getComments()});
	        
		} catch (DaoException e) {
			log.error(e.getMessage());
			intAddNums = 0;
		} finally {
			DAOUtil.closeJDBCConnection(conn);
		}
        return intAddNums;
    }

	@Override
	public int delSpecialCar(String[] carNos) {
		Connection conn = null;
		int intAddNums = 0;
		try {
			conn = DAOUtil.getSQLiteDBConnection();
			
	        StringBuffer delSqlBuffer = new StringBuffer ();
	        for( int i = 0; i < carNos.length; i++){
	        	if( i == 0){
	        		delSqlBuffer.append("delete from specialCar where carNo in (");
	        	}
	        	
	        	delSqlBuffer.append("?");
	        	if( i == carNos.length-1){
	        		delSqlBuffer.append(")");
	        	} else {
	        		delSqlBuffer.append(",");
	        	}
	        }
	        if( delSqlBuffer.length() > 0){
	        	intAddNums = this.execute(conn, delSqlBuffer.toString(), carNos);
	        }
		} catch (DaoException e) {
			log.error(e.getMessage());
		} finally {
			DAOUtil.closeJDBCConnection(conn);
		}
        return intAddNums;
	}

	@Override
	public List<SpecialCar> getAllSpecialCar() {
		List<SpecialCar> listSpecialCar = new ArrayList<SpecialCar>();
		Connection conn = null;
		try {
			conn = DAOUtil.getSQLiteDBConnection();
			
	        String sql = "select * from specialCar";
	        listSpecialCar = this.list(conn, sql, new Extractor<SpecialCar>(){
	        	@Override
				public SpecialCar extract(ResultSet rs) throws SQLException {
	        		SpecialCar specialCar = new SpecialCar();
	        		specialCar.setCarNo(rs.getString("carNo"));
	        		specialCar.setCarOwner(rs.getString("carOwner"));
	        		specialCar.setContact(rs.getString("contact"));
	        		specialCar.setComments(rs.getString("comments"));
	        		return specialCar;
	        	}
	        });
	        
		} catch (DaoException e) {
			log.error(e.getMessage());
		} finally {
			DAOUtil.closeJDBCConnection(conn);
		}
		return listSpecialCar;
	}
	
	@Override
	public List<SpecialCar> listSpecialCarByPage(int pageSize, int pageNo) {
		List<SpecialCar> listSpecialCar = new ArrayList<SpecialCar>();
		Connection conn = null;
		try {
			conn = DAOUtil.getSQLiteDBConnection();
			
	        String sql = "select * from specialCar limit " + pageSize + " offset " + (pageNo - 1) * pageSize;
	        listSpecialCar = this.list(conn, sql, new Extractor<SpecialCar>(){
	        	@Override
				public SpecialCar extract(ResultSet rs)
					throws SQLException {
	        		SpecialCar specialCar = new SpecialCar();
	        		specialCar.setCarNo(rs.getString("carNo"));
	        		specialCar.setCarOwner(rs.getString("carOwner"));
	        		specialCar.setContact(rs.getString("contact"));
	        		specialCar.setComments(rs.getString("comments"));
	        		return specialCar;
	        	}
	        });
	        
		} catch (DaoException e) {
			log.error(e.getMessage());
		} finally {
			DAOUtil.closeJDBCConnection(conn);
		}
		return listSpecialCar;
	}

	@Override
	public SpecialCar getSpecialCar(String carNo) {
		SpecialCar specialCar = null;
		Connection conn = null;;
		try {
			conn = DAOUtil.getSQLiteDBConnection();
			
	        String sql = "select * from specialCar where carNo = ?";
	        specialCar = this.get(conn, sql, new Extractor<SpecialCar>(){
	        	@Override
				public SpecialCar extract(ResultSet rs)
					throws SQLException {
	        		SpecialCar specialCar = new SpecialCar();
	        		specialCar.setCarNo(rs.getString("carNo"));
	        		specialCar.setCarOwner(rs.getString("carOwner"));
	        		specialCar.setContact(rs.getString("contact"));
	        		specialCar.setComments(rs.getString("comments"));
	        		return specialCar;
	        	}
	        },carNo);
	        
		} catch (DaoException e) {
			log.error(e.getMessage());
		} finally {
			DAOUtil.closeJDBCConnection(conn);
		}
		return specialCar;
	}

	@Override
	public int editSpecialCar(SpecialCar specialCar) {
		Connection conn = null;
		int intAddNums = 0;
		try {
			conn = DAOUtil.getSQLiteDBConnection();
			
	        String sql = "update SpecialCar set carOwner = ? , contact = ?, comments=? where carNo = ?";
	        intAddNums = this.execute(conn, sql, new Object[]{specialCar.getCarOwner(),specialCar.getContact(),specialCar.getComments(),specialCar.getCarNo()});
	        
		} catch (DaoException e) {
			log.error(e.getMessage());
			intAddNums = 0;
		} finally {
			DAOUtil.closeJDBCConnection(conn);
		}
        return intAddNums;
	}
	
	public int getTotalSize(){

		Connection conn = null;
		int intAddNums = 0;
		try {
			conn = DAOUtil.getSQLiteDBConnection();
			
	        String sql = "select count(*) from specialCar";
	        intAddNums = this.get(conn, sql, new Extractor<Integer>(){
	        	@Override
				public Integer extract(ResultSet rs)
					throws SQLException {
	        		return rs.getInt(1);
	        	}
	        });
	        
		} catch (DaoException e) {
			log.error(e.getMessage());
			intAddNums = 0;
		} finally {
			DAOUtil.closeJDBCConnection(conn);
		}
        return intAddNums;
	
	}
}
